package cn.baidu.dao;

import cn.baidu.pojo.Room;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class RoomDao {
    Connection conn;

    public RoomDao() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/servlet2?serverTimezone=GMT", "root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addRoom(Room room) {
        try {
            PreparedStatement pst = conn.prepareStatement("INSERT INTO jiudian (id, cr, et, tj, blrz, checkDate, cp) VALUES(null,?,?,?,?,?,?);\n");
            pst.setInt(1,room.getCr());
            pst.setInt(2,room.getEt());
            pst.setString(3,room.getTj());
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            pst.setString(4,format.format(room.getBlrz()));
            pst.setString(5,format.format(room.getCheckDate()));
            pst.setString(6,room.getCp());
            pst.execute();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delRoom(int id){
        try {
            PreparedStatement pst = conn.prepareStatement("delete from jiudian where id = ?");
            pst.setInt(1,id);
            pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public List<Room> findRoom (int id, String tj, String rzsj){
        String sql = "select * from jiudian where 1= 1 ";
        if(id != -1){
            sql += "and id = "+id;
        }
        if(!tj.equals("")){
            sql += "and tj = '"+tj+"'";
        }
        if(!rzsj.equals("")){
            sql += "and tj = '"+rzsj+"'";
        }
        List<Room> rooms = new ArrayList<>();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet resultSet = pst.executeQuery();
            while(resultSet.next()){
                Room room = new Room(resultSet.getInt(1),resultSet.getInt(2),resultSet.getInt(3),resultSet.getString(4),resultSet.getDate(5),resultSet.getDate(6),resultSet.getString(7));
                rooms.add(room);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rooms;
    }

    public void edit(Room room){
        try {
            PreparedStatement pst = conn.prepareStatement("UPDATE jiudian set cr = ? , et = ? , tj = ? , blrz = ? , checkDate = ? , cp = ? WHERE id = ? ;");
            pst.setInt(1,room.getCr());
            pst.setInt(2,room.getEt());
            pst.setString(3,room.getTj());
            String blrz = new SimpleDateFormat("yyyy-MM-dd").format(room.getBlrz());
            String checkdate = new SimpleDateFormat("yyyy-MM-dd").format(room.getCheckDate());
            pst.setString(4,blrz);
            pst.setString(5,checkdate);
            pst.setString(6,room.getCp());
            pst.setInt(7,room.getId());
            pst.execute();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
